package com.vimi8.ebb.service;

import com.vimi8.ebb.model.Address;
import com.vimi8.ebb.model.GeoCodings;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * Created by wangyang on 2016/9/8.
 */
@Mapper
public interface GeoCodingsService {

    /**
     * @param geoCode
     * @param addressCode
     * @return
     */
    @Select("select id,geo_name as geoName,region_lever as regionLever,prov,city,county,village,burg from geo_codings where id like #{geoCode} " +
            "and id!=#{addressCode} ")
    public List<GeoCodings> getGeoCodingsByCode(@Param("addressCode") String addressCode, @Param("geoCode") String geoCode);

    /**
     * 根据当前区域id、当前id的下一个区域id、当前id的下一级区域的级别查询区域
     *
     * @param id    当前区域id
     * @param next  当前id的下一个区域id
     * @param level 当前id的下一级区域的级别
     * @return List<GeoCodings>
     */
    @Select("SELECT id, geo_name AS geoName, region_lever AS regionLever, prov, city, county, village, burg " +
            "FROM geo_codings " +
            "WHERE region_lever=#{level} AND id BETWEEN #{id} AND #{next} ")
    public List<GeoCodings> getGeoCodings(@Param(value = "id") String id, @Param(value = "next") long next, @Param(value = "level") int level);

    /**
     * @param id
     * @return
     */
    @Select("select id,geo_name as geoName,region_lever as regionLever,prov,city,county,village,burg from geo_codings where id=#{id}")
    public GeoCodings getGeoCodingsById(String id);

    /**
     * 根据区域ID查询完整的地址
     *
     * @param areaId
     * @return
     */
    @Select("SELECT GROUP_CONCAT(geo_name SEPARATOR ' ') AS address FROM(" +
            "SELECT geo_name FROM geo_codings WHERE id=(SELECT MID(id,1,4)*100000000 AS id FROM geo_codings WHERE id=#{areaId}) UNION " +
            "SELECT geo_name FROM geo_codings WHERE id=(SELECT MID(id,1,6)*1000000 AS id FROM geo_codings WHERE id=#{areaId}) UNION " +
            "SELECT geo_name FROM geo_codings WHERE id=(SELECT MID(id,1,9)*1000 AS id FROM geo_codings WHERE id=#{areaId}) UNION " +
            "SELECT geo_name FROM geo_codings WHERE id=#{areaId}) AS T")
    public String getAddressWhole(@Param(value = "areaId") String areaId);


    /**
     * 根据地址对象查询区域信息
     *
     * @param address
     * @return
     */
    @Select("<script>" +
            "SELECT id, geo_name AS geoName, region_lever AS regionLever, prov, city, county, village, burg FROM geo_codings " +
            "WHERE geo_name LIKE CONCAT('%',#{address.provence},'%') " +
            "<if test=\"address.city!=null and address.city!='' \"> " +
            "OR geo_name LIKE CONCAT('%',#{address.city},'%') " +
            "</if> " +
            "<if test=\"address.county!=null and address.county!='' \"> " +
            "OR geo_name LIKE CONCAT('%',#{address.county},'%') " +
            "</if> " +
            "<if test=\"address.village!=null and address.village!='' \"> " +
            "OR geo_name LIKE CONCAT('%',#{address.village},'%') " +
            "</if> " +
            "<if test=\"address.burg!=null and address.burg!='' \"> " +
            "OR geo_name LIKE CONCAT('%',#{address.burg},'%') " +
            "</if> " +
            "</script>")
    public List<GeoCodings> getAddressInfo(@Param(value = "address") Address address);
}
